﻿begin transaction SchemaUpdate
print 'transaction "SchemaUpdate" started'

begin try
	create table [Language]
	(LCID nvarchar(4) primary key, Name nvarchar(50))
	print 'Table Language created'

	alter table Word
	add LCID nvarchar(4) null

	alter table Word
	add constraint FK_Word_Language foreign key(LCID) references [Language](LCID)
	print 'Column LCID added to table "Word" and references to table Language'
	
	commit transaction SchemaUpdate
	print 'transaction "SchemaUpdate" committed'		
end try
begin catch	
	rollback transaction SchemaUpdate
	print 'transaction "SchemaUpdate" rolled back'		
end catch
go
			
begin transaction DataUpdate
begin try
	insert into [Language]
	values(1031, 'German')
	print '1031 inserted'

	insert into [Language]
	values (1033, 'English (US)')
	print '1033 inserted'

	update Word
	set LCID = '1031'
	print 'set 1031 as LCID for all words'

	alter table Word
	alter column LCID nvarchar(4) not null
	print 'set LCID in table Word "not null"'

	update [Version]
	set VersionNr = '1.2'
	print 'VersionNr updated to 1.2'
	
	commit transaction DataUpdate
	print 'transaction "DataUpdate" committed'
end try
begin catch
	rollback transaction DataUpdate
	print 'transaction "DataUpdate" rolled back'
end catch